package org.makcro.sms.services.chat.base.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;

import org.makcro.sms.services.chat.base.dto.HistoryItem;
import org.makcro.sms.services.chat.model.Agent;
import org.makcro.sms.services.chat.model.Question;

public class QuestionDAO extends AbstractDAO<Question> implements QuestionDao {
	@PersistenceContext
	public EntityManager	entityManager;
	protected DataSource	dataSource;

	public QuestionDAO() {
		super( Question.class );
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.makcro.sms.services.chat.base.dao.QuestionDao#findUnassigned()
	 */
	@Override
	public List<Question> findUnassigned() {
		@SuppressWarnings("unchecked")
		List<Question> resultList = entityManager.createNamedQuery( "Question.findUnassigned" ).getResultList();
		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.makcro.sms.services.chat.base.dao.QuestionDao#findByAgent(org.makcro
	 * .sms.services.chat.model.Agent)
	 */
	@Override
	public List<org.makcro.sms.services.chat.base.dto.HistoryItem> findByAgent(Agent agent) throws SQLException {
		return findByAgentInternal( agent, null );
	}

	private List<org.makcro.sms.services.chat.base.dto.HistoryItem> findByAgentInternal(Agent agent, int[] pagingParams) throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, "
				+ "(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered, " 
				+ "ag.AgentUserName as AgentsName "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID " 
				+ "INNER JOIN agent ag on qr.AgentID = ag.AgentID " 
				+ "WHERE qr.AgentID = ? "
				+ "AND a.Visable = 1 " + "GROUP BY q.QuestionID " + "ORDER BY a.DateAnswered DESC ";

		// add limit clause
		boolean noPaging = pagingParams == null;
		String limitClause = MessageFormat.format( "LIMIT {0,number,#}, {1,number,#}", noPaging ? 0 : pagingParams[0], noPaging ? 100 : pagingParams[1] );
		// String limitClause = pagingParams == null ? "LIMIT 0, 100" : "LIMIT "
		// + pagingParams[0] + ", " + pagingParams[1];

		query = query + limitClause;

		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement( query );
		statement.setInt( 1, agent.getAgentID() );
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt( "QuestionID" );
			String question = resultSet.getString( "Question" );
			Timestamp dateReceived = resultSet.getTimestamp( "DateReceived" );
			String fromNumber = resultSet.getString( "FromNumber" );
			Integer answerID = resultSet.getInt( "AnswerID" );
			String answer = resultSet.getString( "answer" );
			Timestamp dateAnswered = resultSet.getTimestamp( "DateAnswered" );
			boolean answerDelivered = resultSet.getBoolean( "AnswerDelivered" );
			String agentsName = resultSet.getString( "AgentsName" );
			
			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId( questionID );
			q.setQuestion( question );
			q.setDateReceived( dateReceived );
			q.setFromNumber( fromNumber );
			q.setAnswerId( answerID );
			q.setAnswer( answer );
			q.setDateAnswered( dateAnswered );
			q.setAnswerDelivered( answerDelivered );
			q.setAgentName( agentsName );
			
			result.add( q );
		}
		connection.close();
		return result;
	}

	@Override
	public List<HistoryItem> findByAgent(Agent currentAgent, int[] pagingParams) throws SQLException {

		return findByAgentInternal( currentAgent, pagingParams );
	}

	@Override
	public int findCountByAgent(Agent currentAgent) throws SQLException {
		int result = 0;

		String query = "SELECT COUNT(*) AS total_count " + "FROM ( " + "SELECT q.QuestionID " + "FROM question AS q " + "INNER JOIN answer AS a ON q.QuestionID = a.QuestionID "
				+ "INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID " + "WHERE qr.AgentID = ? AND a.Visable = 1 " + "GROUP BY q.QuestionID) AS q1";
		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement( query );
		statement.setInt( 1, currentAgent.getAgentID() );
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			result = resultSet.getInt( "total_count" );
		}
		connection.close();
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.makcro.sms.services.chat.base.dao.QuestionDao#findAnswered()
	 */
	@Override
	public List<org.makcro.sms.services.chat.base.dto.HistoryItem> findAnswered() throws SQLException {
		return findAsweredInternal( null );
	}

	@Override
	public List<HistoryItem> findAnswered(int[] pagingConfig) throws SQLException {

		return findAsweredInternal( pagingConfig );
	}

	private List<org.makcro.sms.services.chat.base.dto.HistoryItem> findAsweredInternal(int[] pagingLimits) throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, "
				+ "(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered, "
				+ "ag.AgentUserName as AgentsName "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID " 
				+ "INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID "
				+ "INNER JOIN agent ag on qr.AgentID = ag.AgentID " 
				+ "WHERE a.Visable = 1 " + "GROUP BY q.QuestionID " + "ORDER BY a.DateAnswered DESC ";

		// apply paging limits
		boolean noPaging = pagingLimits == null;
		String limitClause = MessageFormat.format( "LIMIT {0,number,#}, {1,number,#}", noPaging ? 0 : pagingLimits[0], noPaging ? 100 : pagingLimits[1] );

		query = query + limitClause;

		Connection connection = this.dataSource.getConnection();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery( query );
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt( "QuestionID" );
			String question = resultSet.getString( "Question" );
			Timestamp dateReceived = resultSet.getTimestamp( "DateReceived" );
			String fromNumber = resultSet.getString( "FromNumber" );
			Integer answerID = resultSet.getInt( "AnswerID" );
			String answer = resultSet.getString( "answer" );
			Timestamp dateAnswered = resultSet.getTimestamp( "DateAnswered" );
			boolean answerDelivered = resultSet.getBoolean( "AnswerDelivered" );
			String agentsName = resultSet.getString( "AgentsName" );
			
			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId( questionID );
			q.setQuestion( question );
			q.setDateReceived( dateReceived );
			q.setFromNumber( fromNumber );
			q.setAnswerId( answerID );
			q.setAnswer( answer );
			q.setDateAnswered( dateAnswered );
			q.setAnswerDelivered( answerDelivered );
			q.setAgentName( agentsName );
			
			result.add( q );
		}
		connection.close();
		return result;
	}

	@Override
	public int findAllAnsweredCount() throws SQLException {
		int result = 0;
		String query = "SELECT COUNT(*)  AS total_count FROM( SELECT q.QuestionID FROM question AS q INNER JOIN answer AS a ON q.QuestionID = a.QuestionID WHERE a.Visable = 1 GROUP BY q.QuestionID) AS q1";
		
		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement( query );
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			result = resultSet.getInt( "total_count" );
		}
		connection.close();
		return result;
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.makcro.sms.services.chat.base.dao.QuestionDao#findHistory(java.lang
	 * .String)
	 */
	@Override
	public List<org.makcro.sms.services.chat.base.dto.HistoryItem> findHistory(String phoneNumber) throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, "
				+ "(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MAX(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered, "
				+ "ag.AgentUserName as AgentsName "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID "
				+ "INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID "
				+ "INNER JOIN agent ag on qr.AgentID = ag.AgentID " 
				+ "where q.FromNumber = ? " + "AND a.Visable = 1 " + "GROUP BY q.QuestionID "
				+ "ORDER BY a.DateAnswered DESC " + "LIMIT 0, 100";

		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement( query );
		statement.setString( 1, phoneNumber );
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt( "QuestionID" );
			String question = resultSet.getString( "Question" );
			Timestamp dateReceived = resultSet.getTimestamp( "DateReceived" );
			String fromNumber = resultSet.getString( "FromNumber" );
			Integer answerID = resultSet.getInt( "AnswerID" );
			String answer = resultSet.getString( "answer" );
			Timestamp dateAnswered = resultSet.getTimestamp( "DateAnswered" );
			boolean answerDelivered = resultSet.getBoolean( "AnswerDelivered" );
			String agentsName = resultSet.getString( "AgentsName" );
			
			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId( questionID );
			q.setQuestion( question );
			q.setDateReceived( dateReceived );
			q.setFromNumber( fromNumber );
			q.setAnswerId( answerID );
			q.setAnswer( answer );
			q.setDateAnswered( dateAnswered );
			q.setAnswerDelivered( answerDelivered );
			q.setAgentName( agentsName );
			
			result.add( q );
		}
		connection.close();
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.makcro.sms.services.chat.base.dao.QuestionDao#findByCoreId(java.lang
	 * .Long)
	 */
	@Override
	public Question findByCoreId(Long referenceId) {
		Question question;
		try {
			question = (Question) entityManager.createNamedQuery( "Question.findByCoreId" ).setParameter( "coreId", referenceId ).getSingleResult();
		}
		catch (NoResultException e) {
			return null;
		}
		return question;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.makcro.sms.services.chat.base.dao.QuestionDao#setDataSource(javax
	 * .sql.DataSource)
	 */
	@Override
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	

}